package org.cyz.tools.common.util.sqlite;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.file.FileMode;
import org.cyz.tools.common.bean.DBInfo;
import org.cyz.tools.common.util.CamelCaseUtil;
import org.cyz.tools.core.assembly.log.Log;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.*;

/**
 * @author ChenYaZhou
 * @title: SQLiteUtil
 * @projectName RedisGUI
 * @description: sqlite工具
 * @date 2021/5/8 14:23
 */
public class SQLiteUtil {

    private static String DRIVDE = "org.sqlite.JDBC";
    private static String filePath = System.getProperty("user.dir") + "/redis-gui.db";
    private static String conn = "jdbc:sqlite:" + filePath;

    //连接池
    private static LinkedList<Connection> pools;
    private static int poolSize = 1;

    private final static String DELETE_TABLE = "drop table %s";
    private final static String QUERY_ALL = "select * from %s";


    private static class LazyHolder {
        private static final SQLiteUtil INSTANCE = new SQLiteUtil();
    }


    /**
     * 初始化
     */
    static {
        try {
            Class.forName(DRIVDE);
            init();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 判断db是否存在如果不存在则创建
     */
    private static void dbExist() {
        File file = new File(filePath);
        if (!file.exists()) {
            FileUtil.createRandomAccessFile(file, FileMode.rw);
        }
    }

    /**
     * 初始化
     *
     * @throws SQLException
     */
    private static void init() throws SQLException {
        dbExist();
        pools = new LinkedList<>();
        for (int i = 0; i < poolSize; i++) {
            pools.addLast(DriverManager.getConnection(conn));//连接数据库,不存在则创建
        }
    }

    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    private static Connection getConnection() {
        Connection conn =null;
        try {
            if(pools.size()>0){
                conn = pools.getLast();
            }else{
                init();
                conn = pools.getLast();
            }
            if (conn.isClosed()) {
                pools.remove(conn);
                if(pools.size()>0){
                    conn = pools.getLast();
                }else{
                    init();
                    conn = pools.getLast();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }


    /**
     * 获取单例
     *
     * @return
     */
    public static final SQLiteUtil getInstance() {
        dbExist();
        return LazyHolder.INSTANCE;
    }

    /**
     * 删除表
     *
     * @param tableName
     */
    public boolean dropTable(final String tableName) {
        Connection conn = getConnection();
        Statement statement = null;
        try {
            statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(String.format(DELETE_TABLE, tableName));
            return rs.next();
        } catch (Exception e) {
            Log.error("表删除失败{}", e.getMessage());

            e.printStackTrace();
        } finally {
            try {
                statement.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 判断数据表是否存在
     *
     * @param table
     * @return
     */
    public static boolean tableIsExist(Class table) {
        boolean result = false;
        if (table == null) {
            return false;
        }
        Connection conn=null;
        Statement statement=null;
        try {
            conn=getConnection();
            statement=conn.createStatement();
            String tableName = table.getName().replaceAll("\\.", "_");
            String sql = "select count(*) as c from db_test5 where type ='table' and name ='" + tableName.trim() + "'";
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                int count = rs.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }
        } catch (Exception e) {
            Log.error(SQLiteUtil.class.getSimpleName(), e.getMessage());
        }finally {
            try {
                conn.close();
                statement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 如果表不存在就创建
     *
     * @param clazz
     * @return
     */
    public static boolean createTableIfNotExits(Class clazz) {
        if (!tableIsExist(clazz)) {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("(");
            Connection conn=null;
            Statement statement=null;
            try {
                conn=getConnection();
                statement=conn.createStatement();
                String fieldName = null;
                // 获取对象属性
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    fieldName = field.getName();
                    String upperChar = fieldName.substring(0, 1).toUpperCase();
                    String anotherStr = fieldName.substring(1);
                    String methodName = "get" + upperChar + anotherStr;
                    Method method = clazz.getMethod(methodName, new Class[]{});
                    method.setAccessible(true);
//                    Object value = method.invoke(clazz, new Object[]{});
                    // 这里可以编写你的业务代码
                    if ("id".equalsIgnoreCase(fieldName)) {
                        stringBuffer.append(fieldName + " " + getType(field.getType().toString()) + " primary key autoincrement,");
                    } else {
                        stringBuffer.append(fieldName + " " + getType(field.getType().toString()) + ",");
                    }
                }
                String sql = "create table if not exists "
                        + getTableName(clazz.getSimpleName()) + stringBuffer.substring(0, stringBuffer.length() - 1) + ")";
//                ResultSet rs = getConnection().createStatement().executeQuery(sql);
//                return rs.next();
               return statement.execute(sql);
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                try {
                    conn.close();
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return false;
    }


    /**
     * 获取驼峰的表名
     *
     * @param simpleName
     * @return
     */
    private static String getTableName(String simpleName) {
        return CamelCaseUtil.toUnderlineCase(simpleName);
    }

    /**
     * 获取sqlite类型
     *
     * @param key
     * @return
     */
    private static String getType(String key) {
        Map<String, String> maps = new HashMap<>();
        maps.put("java.lang.Integer", "integer");
        maps.put("java.lang.Double", "double");
        maps.put("java.lang.Float", "float");
        maps.put("java.lang.Long", "long");
        maps.put("java.lang.Short", "short");
        maps.put("java.lang.Byte", "byte");
        maps.put("java.lang.Boolean", "boolean");
        maps.put("java.lang.String", "varchar(255)");
        maps.put("class java.lang.String", "varchar(255)");
        maps.put("int", "integer");
        return maps.get(key) == null ? key : maps.get(key);
    }


    /**
     * 执行sql
     *
     * @param sql
     * @return
     */
    public static boolean executeQuery(String sql) {
        Statement statement = null;
        Connection conn = null;
        try {
            conn = getConnection();
            statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                conn.close();
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    public static boolean execute(String sql) {
        Statement statement = null;
        Connection conn = null;
        try {
            conn = getConnection();
            statement = conn.createStatement();
            return statement.execute(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                conn.close();
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    /**
     * 查询所有
     *
     * @param clazz
     * @return
     */
    public static List<Object> queryAll(Object clazz) {
        Statement statement = null;
        Connection conn = null;
        List<Object> colls = new ArrayList<>();
        try {
            conn = getConnection();
            statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(String.format(QUERY_ALL, getTableName(clazz.getClass().getSimpleName())));
            List<Map<String, String>> list = getFiledsInfo(clazz);
            while (rs.next()) {
                for (Map map : list) {
                    switch (map.get("type").toString().replaceAll("class", "").trim()) {
                        case "java.lang.String":
                            String fieldName=map.get("name").toString();
                            Field field = clazz.getClass().getDeclaredField(fieldName);
                            field.setAccessible(true);
                            String val = rs.getString(map.get("name").toString());
                            field.set(clazz, String.valueOf(val));
                            break;
                        case "int":
                            fieldName=map.get("name").toString();
                            Field fieldInt = clazz.getClass().getDeclaredField(fieldName);
                            fieldInt.setAccessible(true);
                            int i = rs.getInt(map.get("name").toString());
                            fieldInt.setInt(clazz,Integer.valueOf(i));
                            break;
                    }
                }
                colls.add(clazz);
            }
            return colls;
        } catch (Exception e) {
            Log.error(SQLiteUtil.class.getSimpleName(),"异常:[{}]",e.getMessage());
        } finally {
            try {
                conn.close();
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    public static Object find(String sql, Class clazz) {
        Statement statement = null;
        Connection conn = null;
        try {
            conn = getConnection();
            statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            List<Map<String, String>> list = getFiledsInfo(clazz);
            while (rs.next()) {
                for (Map map : list) {
                    switch (map.get("type").toString().replaceAll("class", "").trim()) {
                        case "java.lang.String":
                            Field field = clazz.getDeclaredField(rs.getString(map.get("name").toString()));
                            field.setAccessible(true);
                            String val = rs.getString(map.get("name").toString());
                            field.set(clazz, val);
                            break;
                        case "int":
                            Field fieldInt = clazz.getDeclaredField(rs.getString(map.get("name").toString()));
                            fieldInt.setAccessible(true);
                            int i = rs.getInt(map.get("name").toString());
                            fieldInt.set(clazz, i);
                            break;
                    }
                }
                return clazz;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 获取属性名数组
     */
    private String[] getFiledName(Class clazz) {
        Field[] fields = clazz.getClass().getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            System.out.println(fields[i].getType());
            fieldNames[i] = fields[i].getName();
        }
        return fieldNames;
    }

    /**
     * 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list
     */
    private static List<Map<String, String>> getFiledsInfo(Object o) {
        Field[] fields = o.getClass().getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        List<Map<String, String>> list = new ArrayList();
        Map infoMap = null;
        for (Field field : fields) {
            String fieldName = field.getName();
            int fieldValue = field.getModifiers();//如：private、static、final等
            Modifier.isStatic(fieldValue);//看此修饰符是否为静态(static)
            infoMap = new HashMap();
            infoMap.put("type", field.getType().toString());
            infoMap.put("name", fieldName);
//            infoMap.put("value", getFieldValueByName(field.getName(), o));
            list.add(infoMap);
        }
        return list;
    }

    /**
     * 获取对象的所有属性值，返回一个对象数组
     */
    public Object[] getFiledValues(Class o) {
        String[] fieldNames = this.getFiledName(o);
        Object[] value = new Object[fieldNames.length];
        for (int i = 0; i < fieldNames.length; i++) {
            value[i] = this.getFieldValueByName(fieldNames[i], o);
        }
        return value;
    }

    /**
     * 根据属性名获取属性值
     */
    private Object getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = o.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(o, new Object[]{});
            return value;
        } catch (Exception e) {
            Log.error(SQLiteUtil.class.getSimpleName(),e.getMessage());
            return null;
        }
    }


    /**
     * 获取记录的id最大值
     *
     * @param tabName
     * @return
     */
    public static int getMaxId(String tabName) {
        Statement statement = null;
        Connection conn = null;
        int id = -1;
        try {
            conn = getConnection();
            statement = conn.createStatement();
            ResultSet rSet = statement.executeQuery("select id from " + tabName + " ORDER BY id desc");
            while (rSet.next()) {
                Log.info(SQLiteUtil.class.getSimpleName(),rSet.getInt(1) );
                id = rSet.getInt(1);
                break;
            }
        } catch (SQLException throwables) {
            Log.error(SQLiteUtil.class.getSimpleName(), throwables.getMessage());
        } finally {
            try {
                conn.close();
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return id;
    }

    /**
     * 保存
     *
     * @param clazz
     * @return
     */
    public static int save(Object clazz) {
        int id = 0;
        StringBuffer _fieds = new StringBuffer();
        StringBuffer _values = new StringBuffer();
        _fieds.append("(");
        _values.append(" values(");
        Statement statement = null;
        Connection conn = null;
        try {
            conn = getConnection();
            statement = conn.createStatement();
            String fieldName = null;
            // 获取对象属性
            Field[] fields = clazz.getClass().getDeclaredFields();
            for (Field field : fields) {
                fieldName = field.getName();
                String upperChar = fieldName.substring(0, 1).toUpperCase();
                String anotherStr = fieldName.substring(1);
                String methodName = "get" + upperChar + anotherStr;
                Method method = clazz.getClass().getMethod(methodName, new Class[]{});
                method.setAccessible(true);
                Object value = method.invoke(clazz, new Object[]{});
                switch (field.getType().toString()) {
                    case "class java.lang.String":
                        _fieds.append("'" + fieldName + "',");
                        _values.append("'" + value + "',");
                        break;
                    default:
                        _fieds.append(fieldName + ",");
                        _values.append(value + ",");
                        break;
                }
            }
            String sql = "insert into "
                    + getTableName(clazz.getClass().getSimpleName()) + _fieds.substring(0, _fieds.length() - 1) + ") " + _values.substring(0, _values.length() - 1) + ")";
            int count = statement.executeUpdate(sql);
            Log.info(SQLiteUtil.class.getSimpleName(),"保存", count);
            return count;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                conn.close();
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }


    /**
     * 判断某张表是否存在
     * @param tableName 表名
     * @return
     */
    public static boolean tableIsExist(String tableName) {
        if (tableName == null) {
            return false;
        }
        Connection conn = null;
        Statement statement=null;
        try {
            conn=getConnection();
            statement=conn.createStatement();
            //这里表名可以是Sqlite_master
            String sql = "select count(*) as c from sqlite_master where name ='" + tableName.trim() + "' ";
            ResultSet rSet = statement.executeQuery(sql);
            return rSet.next();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
                statement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return false;
    }

    public static void main(String[] args) {
        List list = SQLiteUtil.getFiledsInfo(DBInfo.class);
        System.out.println(list);
    }


}


